﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using Model.Entities;

namespace Model
{
    public class TrackProvider : DataAccess
    {
        protected TrackDetails GetTrackFromReader(IDataReader reader)
        {
            DateTime date = GetDate(reader["TimeTrack"]);
            var track = new TrackDetails((int)reader["TrackID"], reader["TrackName"].ToString(), 
                        (int)reader["AlbumID"], reader["AlbumName"].ToString(), (int) reader["Rating"], 
                        date);
            return track;
        }

        public TrackDetails GetEmptyTrack()
        {
            var emptyTrack = new TrackDetails
                                 {
                                     TrackId = 0,
                                     TrackName = "Empty",
                                     AlbumId = 0,
                                     AlbumName = "Empty",
                                     Rating = 0
                                 };
            return emptyTrack;
        }

        public List<TrackDetails> GetTracks()
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("GetTracks", con) { CommandType = CommandType.StoredProcedure };
            var tracks = new List<TrackDetails>();
            
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    tracks.Add(GetTrackFromReader(reader));
                }
                reader.Close();
            }
            con.Close();
            return tracks;
        }

        public int InsertTrack(TrackDetails track)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("AddTrack", con) { CommandType = CommandType.StoredProcedure };

            cmd.Parameters.Add("@TrackName", SqlDbType.NVarChar).Value = track.TrackName;
            cmd.Parameters.Add("@AlbumID", SqlDbType.NVarChar).Value = track.AlbumId;
            cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = track.Rating;
            cmd.Parameters.Add("@TimeTrack", SqlDbType.DateTime).Value = track.TimeTrack;
            cmd.Parameters.Add("@TrackID", SqlDbType.Int).Direction = ParameterDirection.Output;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            return (int)cmd.Parameters["@TrackID"].Value;
        }

        public void UpdateTrack(TrackDetails track)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("UpdateTrack", con) { CommandType = CommandType.StoredProcedure };

            cmd.Parameters.Add("@TrackName", SqlDbType.NVarChar).Value = track.TrackName;
            cmd.Parameters.Add("@AlbumID", SqlDbType.NVarChar).Value = track.AlbumId;
            cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = track.Rating;
            cmd.Parameters.Add("@TimeTrack", SqlDbType.DateTime).Value = track.TimeTrack;
            cmd.Parameters.Add("@TrackID", SqlDbType.Int).Value = track.TrackId;
            
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void DeleteTrack(int trackId)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("DeleteTrack", con) { CommandType = CommandType.StoredProcedure };

            cmd.Parameters.Add("@TrackID", SqlDbType.Int).Value = trackId;
           
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void DeleteTrack(TrackDetails track)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("DeleteTrack", con) { CommandType = CommandType.StoredProcedure };

            cmd.Parameters.Add("@TrackID", SqlDbType.Int).Value = track.TrackId;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public List<TrackDetails> GetTracksByName(string trackName)
        {
            if (string.IsNullOrEmpty(trackName))
                return GetTracks();
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("SearchTrackByName", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@TrackName", SqlDbType.NVarChar).Value = trackName;
            var tracks = new List<TrackDetails>();

            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    tracks.Add(GetTrackFromReader(reader));
                }
                reader.Close();
            }
            con.Close();
            return tracks;
        }

        public TrackDetails GetTrackById(int trackId)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("SearchTrackById", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@TrackID", SqlDbType.Int).Value = trackId;
            var track = new TrackDetails();

            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    track = GetTrackFromReader(reader);
                }
                reader.Close();
            }
            con.Close();
            return track;
        }

        public List<TrackDetails> GetTracksByAlbumId(int albId)
        {
            var con = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("SearchTracksByAlbum", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@AlbumID", SqlDbType.Int).Value = albId;
            var tracks = new List<TrackDetails>();

            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    tracks.Add(GetTrackFromReader(reader));
                }
                reader.Close();
            }
            con.Close();
            return tracks;
        }
    }
}
